package cn.com.dashihui.api.service;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.api.base.BaseService;
import cn.com.dashihui.api.dao.ApiClient;
import cn.com.dashihui.api.dao.Category;
import cn.com.dashihui.api.dao.Feedback;
import cn.com.dashihui.api.dao.Store;
import cn.com.dashihui.api.dao.Version;

public class CommonService  extends BaseService{
	
	/**
	 * 查询指定设备ID的设备信息
	 */
	public ApiClient findClientByUqid(String uqid){
		return ApiClient.me().findFirst("SELECT * FROM t_api_client WHERE uqid=?",uqid);
	}
	
	/**
	 * 查询出指定平台的，最新的版本信息
	 */
	public Version findLastVersion(String type){
		return Version.me().findFirst("SELECT * FROM t_sys_version WHERE TYPE=? ORDER BY releaseDate DESC",type);
	}
	
	/**
	 * 查询指定数量的店铺信息，供客户端展示（考虑到客户端上有些用户禁用定位）
	 */
	public List<Record> findAllLBSData(String keyword){
		String sql = "SELECT * FROM t_dict_lbs lbs WHERE 1=1";
		List<Object> params = new ArrayList<Object>();
		//判断关键字搜索
		if(!StrKit.isBlank(keyword)){
			sql = sql + " AND lbs.title LIKE ?";
			params.add("%"+keyword+"%");
		}
		return Db.find(sql,params.toArray());
	}
	
	
	/**
	 * 根据店铺ID查询相应的店铺信息
	 */
	public Store findStoreById(int storeid){
		return Store.me().findFirst("SELECT store.*,p.name provinceName,c.name cityName,a.name areaName FROM t_dict_store store LEFT JOIN t_dict_city p ON store.province=p.id LEFT JOIN t_dict_city c ON store.city=c.id LEFT JOIN t_dict_city a ON store.area=a.id WHERE store.id=?",storeid);
	}
	
	/**
	 * 查找出所有产品分类，及各分类下商品数量
	 */
	public List<Category> findAllCategory(int storeid, int isSelf){
		String sql = "";
		if(isSelf == 1) {
			sql = "SELECT r.categoryId,r.categoryType,r.categoryFatherId,r.categoryNum code,r.categoryName name,r.categoryNo,IFNULL(gtotal.total,0) goodscount"
					+ " FROM t_dict_category r LEFT JOIN"
					+ " (SELECT * FROM"
					+ " (SELECT g.categoryonid categoryId,count(*) total FROM t_bus_goods g WHERE g.isSelf=1 AND g.state=1 GROUP BY g.categoryonid UNION"
					+ " SELECT g.categorytwid categoryId,count(*) total FROM t_bus_goods g WHERE g.isSelf=1 AND g.state=1 GROUP BY g.categorytwid"
//					+ " SELECT g.categorythid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categorythid UNION"
//					+ " SELECT g.categoryfoid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categoryfoid"
					+ ") gtotal) gtotal"
					+ " ON r.categoryId=gtotal.categoryId ORDER BY r.categoryNo ASC";
			return trim(Category.me().find(sql));
		} else {
			sql = "SELECT r.categoryId,r.categoryType,r.categoryFatherId,r.categoryNum code,r.categoryName name,r.categoryNo,IFNULL(gtotal.total,0) goodscount"
					+ " FROM t_dict_category r LEFT JOIN"
					+ " (SELECT * FROM"
					+ " (SELECT g.categoryonid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categoryonid UNION"
					+ " SELECT g.categorytwid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categorytwid "
//					+ " SELECT g.categorythid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categorythid UNION"
//					+ " SELECT g.categoryfoid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categoryfoid"
					+ ") gtotal) gtotal"
					+ " ON r.categoryId=gtotal.categoryId ORDER BY r.categoryNo ASC";
			return trim(Category.me().find(sql,storeid,storeid));
		}
	}
	/**
	 * 对分类进行等级分类
	 */
	private List<Category> trim(List<Category> CategoryList){
		if(CategoryList==null||CategoryList.size()<=0){
			return null;
		}
		List<Category> listTypeone=new ArrayList<Category>();
		List<Category> listTypetwo=new ArrayList<Category>();
		List<Category> listTypethree=new ArrayList<Category>();
		List<Category> listTypefour=new ArrayList<Category>();
		
		//进行分类
		for(Category category : CategoryList){
			if(category.getInt("categoryType").intValue()==1){
				listTypeone.add(category);
			}else if(category.getInt("categoryType").intValue()==2){
				listTypetwo.add(category);
			}else if(category.getInt("categoryType").intValue()==3){
				listTypethree.add(category);
			}else{
				listTypefour.add(category);
			}
		}
		//组合4-3
		if(listTypefour.size()!=0){
			for(Category c4 : listTypefour){
				for(Category c3 : listTypethree){
					if(c4.getInt("categoryFatherId").equals(c3.getInt("categoryId"))){
						c3.addChild(c4);
						break;
					}
				}
			}
		}
		//组合3-2
		if(listTypethree.size()!=0){
			for(Category c3 : listTypethree){
				for(Category c2 : listTypetwo){
					if(c3.getInt("categoryFatherId").equals(c2.getInt("categoryId"))){
						c2.addChild(c3);
						break;
					}
				}
			}
		}
		//组合2-1
		if(listTypetwo.size()!=0){
			for(Category c2 : listTypetwo){
				for(Category c1 : listTypeone){
					if(c2.getInt("categoryFatherId").equals(c1.getInt("categoryId"))){
						c1.addChild(c2);
						break;
					}
				}
			}
		}
		return listTypeone;
	}
	
	/**
	 * 记录意见反馈内容
	 */
	public boolean feedback(String context,int userid){
		return new Feedback().set("userid", userid).set("context", context).save();
	}
	
	/**
	 * 查询所有版本
	 */
	public List<Record> queryAllVersion() {
		return Db.find("select * from t_sys_version order by releaseDate DESC");
	}
	
	/**
	 * 查询兑换比例
	 * @return
	 */
	public Record queryScale() {
		return Db.findFirst("select * from t_bus_user_change_scale order by date DESC");
	}
	
	/**
	 * 查询充值奖励比例
	 * @return
	 */
	public Record queryAward(String code) {
		return Db.findFirst("select * from t_sys_config where code = ? and state=2",code);
	}
	
	/**
	 * 
	 * @return
	 */
	public List<Record> rechargeList() {
		return Db.find("select * from t_bus_goods_ecard where state=2 and pstate=1 and countnum>0");
	}
	
}
